#!/bin/bash

# 定义变量
HIVE_DB="stuinfo"
MYSQL_CONN="jdbc:mysql://45.152.67.160:3307/stuinfo"
MYSQL_USER="root"
MYSQL_PASS="123456"
NUM_MAPPERS=1



MYSQL_USER="root"
MYSQL_PASSWORD="123456"
MYSQL_DATABASE="stuinfo"
MYSQL_HOST="45.152.67.160"
MYSQL_PORT="3307"  # 默认端口是3306，如果你的数据库使用的是默认端口，可以省略这一行

# 需要清空的表
TABLES=("class_gender_summary" "gender_summary" "semester_class_gender_summary" "semester_gender_summary" "status_gender_summary" "students_info")

# 遍历每个表并执行 TRUNCATE
for TABLE in "${TABLES[@]}"; do
    echo "Truncating table $TABLE..."
    mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE -e "TRUNCATE TABLE $TABLE;"
    if [ $? -eq 0 ]; then
        echo "Successfully truncated table $TABLE."
    else
        echo "Failed to truncate table $TABLE."
    fi
done
echo "All specified tables have been truncated."


# Truncate tables
truncate_tables=(
    "201_detail"
    "202_detail"
    "211_detail"
    "212_detail"
    "213_detail"
    "214_detail"
    "class_gender_summary"
    "gender_summary"
    "semester_class_gender_summary"
    "semester_gender_summary"
    "status_gender_summary"
    "stu_unclean_detail"
    "stu_detail"
    "stu_etl"
)

for table in "${truncate_tables[@]}"; do
    echo "Truncating table $table..."
    hive -e "TRUNCATE TABLE $table;"
    echo "Table $table truncated."
done

# 插入数据到stu_etl
echo "Inserting data into stu_etl..."
hive -e "
INSERT INTO stu_etl
SELECT courseid, name, gender, dateofbirth, studentid, semester, gpa, enrollmentstatus
FROM stutable
WHERE gender <= 1
GROUP BY courseid, name, gender, dateofbirth, studentid, semester, gpa, enrollmentstatus;"
echo "Data inserted into stu_etl."

# 插入数据到201_detail到214_detail
details=("201" "202" "211" "212" "213" "214")
for detail in "${details[@]}"; do
    echo "Inserting data into ${detail}_detail..."
    hive -e "
    INSERT INTO TABLE ${detail}_detail
    SELECT
        a.id,
        a.name,
        a.content,
        a.time,
        b.courseid,
        b.gender,
        b.dateofbirth,
        b.studentid,
        b.semester,
        b.gpa,
        b.enrollmentstatus
    FROM
        \`${detail}\` a
    INNER JOIN (
        SELECT
            courseid,
            name,
            gender,
            dateofbirth,
            studentid,
            semester,
            gpa,
            enrollmentstatus,
            ROW_NUMBER() OVER (PARTITION BY name ORDER BY studentid) as rn
        FROM
            stu_etl
    ) b
    ON
        a.name = b.name
    WHERE
        b.rn = 1;"
    echo "Data inserted into ${detail}_detail."
done

# 插入数据到stu_detail
echo "Inserting data into stu_unclean_detail..."
hive -e "
INSERT INTO TABLE stu_unclean_detail
SELECT * FROM 201_detail
UNION ALL
SELECT * FROM 202_detail
UNION ALL
SELECT * FROM 211_detail
UNION ALL
SELECT * FROM 212_detail
UNION ALL
SELECT * FROM 213_detail
UNION ALL
SELECT * FROM 214_detail;"
echo "Data inserted into stu_unclean_detail."

#清洗脚本
echo "Cleaning data into stu_detail..."
hive -e "
USE $HIVE_DB;
INSERT INTO TABLE stu_detail
SELECT id, name, content, time, courseid, gender, dateofbirth, studentid, semester, gpa, enrollmentstatus
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY name) as row_num
    FROM stu__unclean_detail
) t
WHERE t.row_num = 1;
echo "Data inserted into stu_detail."

# 插入数据到gender_summary
echo "Inserting data into gender_summary...${NC}"
hive -e "
USE $HIVE_DB;
INSERT INTO TABLE gender_summary
SELECT
    COUNT(CASE WHEN gender = 1 THEN 1 END) AS male_count,
    COUNT(CASE WHEN gender = 0 THEN 1 END) AS female_count,
    CURRENT_TIMESTAMP AS update_time
FROM
    stu_detail;"
echo "Data inserted into gender_summary."


# 插入数据到class_gender_summary
echo "Inserting data into class_gender_summary..."
hive -e "
USE $HIVE_DB;
INSERT INTO TABLE class_gender_summary
SELECT
    courseid AS class_id,
    COUNT(CASE WHEN gender = 1 THEN 1 END) AS male_count,
    COUNT(CASE WHEN gender = 0 THEN 1 END) AS female_count,
    CURRENT_TIMESTAMP AS update_time
FROM
    stu_detail
GROUP BY
    courseid;"
echo "Data inserted into class_gender_summary."

# 插入数据到semester_gender_summary
echo "Inserting data into semester_gender_summary..."
hive -e "
USE $HIVE_DB;
INSERT INTO TABLE semester_gender_summary
SELECT
    semester,
    SUM(CASE WHEN gender = 1 THEN 1 ELSE 0 END) AS male_count,
    SUM(CASE WHEN gender = 0 THEN 1 ELSE 0 END) AS female_count,
    CURRENT_TIMESTAMP AS update_time
FROM
    stu_detail
GROUP BY
    semester;"
echo "Data inserted into semester_gender_summary."

# 插入数据到status_gender_summary
echo "Inserting data into status_gender_summary..."
hive -e "
USE $HIVE_DB;
INSERT INTO TABLE status_gender_summary
SELECT
    enrollmentstatus AS status,
    SUM(CASE WHEN gender = 1 THEN 1 ELSE 0 END) AS male_count,
    SUM(CASE WHEN gender = 0 THEN 1 ELSE 0 END) AS female_count,
    CURRENT_TIMESTAMP AS update_time
FROM
    stu_detail
GROUP BY
    enrollmentstatus;"
echo "Data inserted into status_gender_summary."

# 插入数据到semester_class_gender_summary
echo "Inserting data into semester_class_gender_summary..."
hive -e "
USE $HIVE_DB;
INSERT INTO TABLE semester_class_gender_summary
SELECT
    semester,
    courseid AS class_id,
    SUM(CASE WHEN gender = 1 THEN 1 ELSE 0 END) AS male_count,
    SUM(CASE WHEN gender = 0 THEN 1 ELSE 0 END) AS female_count,
    CURRENT_TIMESTAMP AS update_time
FROM
    stu_detail
GROUP BY
    semester,
    courseid;"
echo "Data inserted into semester_class_gender_summary."

# Sqoop export commands
sqoop_exports=(
    "gender_summary"
    "class_gender_summary"
    "semester_gender_summary"
    "status_gender_summary"
    "semester_class_gender_summary"
)

for table in "${sqoop_exports[@]}"; do
    echo "Exporting table $table to MySQL..."
    sqoop export \
        --connect $MYSQL_CONN \
        --username $MYSQL_USER \
        --password $MYSQL_PASS \
        --table $table \
        --export-dir /user/hive/warehouse/$HIVE_DB.db/$table \
        --input-fields-terminated-by '\t' \
        --input-lines-terminated-by '\n' \
        --input-null-string '\\N' \
        --input-null-non-string '\\N' \
        --num-mappers $NUM_MAPPERS
    echo "Table $table exported to MySQL."
done

sqoop export --connect jdbc:mysql://45.152.67.160:3307/stuinfo --username root --password 123456 --table student_info --export-dir /user/hive/warehouse/stuinfo.db/stu_detail --input-fields-terminated-by '\t' --input-lines-terminated-by '\n' --input-null-string '\\N' --input-null-non-string '\\N'
    echo "Table stu_detail exported to MySQL."